package io.potato.ts.service;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import io.potato.ts.domain.SmsSendingRecord;
import io.potato.ts.sms.SmsApiStatus;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import io.potato.core.CrudService;
import io.potato.core.util.LdtUtil;
import io.potato.core.util.SmsUtil;
import io.potato.core.util.TableUtil;
import io.potato.ts.common.Constants;
import io.potato.ts.domain.SmsSendedRecord;
import io.potato.ts.domain.SmsStatusReport;
import io.potato.ts.domain.User;
import io.potato.ts.repository.SmsSendedRecordRepository;


/**
 * 已发送的短信服务类
 * 
 * @author timl
 *
 * <p>2019-01-07 15:13:58</p>
 */
@Service
@Transactional
public class SmsSendedRecordService extends CrudService<SmsSendedRecord, Long> {

	@Autowired
	SmsSendedRecordRepository repository;
	
	@Autowired
	JdbcTemplate jdbcTemplate;

	@Override
	protected JpaRepository<SmsSendedRecord, Long> getRepository() {
		return repository;
	}
	
	public SmsSendedRecord findByMsgId(String msgId) {
		return repository.findByHwMsgId(msgId);
	}

	/**
	 * 更新已发记录的短信接收状态
	 * @param smsReport  短信报告
	 */
	public void updateReceiveStatus(SmsStatusReport smsReport) {
		if (this.needUpdateReceiveStatus(smsReport)) {
			LocalDateTime now = LocalDateTime.now();
			String tableName = TableUtil.getSendedRecordTable(smsReport.getUserCode(), now);
			int updateRows = this.updateReceiveStatus(tableName, smsReport.getHwMsgId(), smsReport.getStatus(), smsReport.getStatusDesc());
			if (updateRows <= 0) {
				LocalDateTime yestoday = now.minusDays(1L);
				tableName = TableUtil.getSendedRecordTable(smsReport.getUserCode(), yestoday);
				this.updateReceiveStatus(tableName, smsReport.getHwMsgId(), smsReport.getStatus(), smsReport.getStatusDesc());
			}
		}
	}

	/**
	 * 查询用户的已发送记录
	 * @param pageable  分页信息
	 * @param user  用户
	 * @param destNo  接收号码
	 * @param content  短信内容过滤
	 * @param start  开始时间
	 * @param end  结束时间
	 * @param taskName  发送任务
	 * @return
	 */
	public Page<Map<String, Object>> findUserSendedRecord(
			Pageable pageable, User user,
			String destNo, String content, 
			LocalDateTime start, LocalDateTime end, String taskName) {

		// 时间不合法
		if (!start.isBefore(end)) {
			return new PageImpl<>(new ArrayList<>(), pageable, 0);
		}
		
		String userCode = user.getUserCode();

		// 根据查询条件生成sql
		String where = this.getWhere(userCode, destNo, content, start, end, taskName);
		String select = " id, user_id, dest_num, send_status, task_name, dealer_id, " +
				"content, submit_time, send_time, receive_status, receive_status_desc ";

		// 生成分表查询sql
		String subTable = this.getSubSql(userCode, select, where, start, end);
		
		String sql = "select t.id, t.dest_num destNum, t.task_name taskName, t.content, t.submit_time submitTime, "
				+ " ifnull(t1.name, t2.real_name) destName, dealer_id dealerId, " +
				"receive_status_desc receiveStatusCode, send_status sendStatus, receive_status receiveStatus, " +
		        "case when t.send_status = 0 then '成功' else '失败' end sendStatusDesc, \n" +
		        "case when t.receive_status = 0 then '成功' when t.receive_status=-1 then '等待接收' else '失败' end receiveStatusDesc \n" +
				" from \n" + 
				subTable + " t \n" + 
				"left join t_contacts t1\n" + 
				"on t.user_id = t1.user_id and t.dest_num = t1.mobile_no \n" + 
				"left join t_user t2\n" + 
				"on t.dest_num = t2.mobile_no \n" + 
 				" group by t.id order by submit_time desc, t.dest_num limit " +
				pageable.getOffset() + ", " + 
 				pageable.getPageSize();

		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);

		//list.forEach(m -> System.out.println(m));

		processStatusDesc(list);
		
		String countSql = "select count(*) from " + subTable + " t";
		long count = this.jdbcTemplate.queryForObject(countSql, Long.class);
		
		return new PageImpl<>(list, pageable, count);
	}

	private void processStatusDesc(List<Map<String, Object>> list) {
		if (list == null || list.isEmpty()) {
			return;
		}

		for (Map<String, Object> map : list) {
			int sendStatus = map.containsKey("sendStatus") ? NumberUtils.toInt("" + map.get("sendStatus"), 0) : 0;

			if (sendStatus > 0) {
				map.put("sendStatusDesc", SmsApiStatus.getSendStatusDesc(sendStatus));
				map.put("receiveStatusDesc", "-");
				continue;
			}

			int receiveStatus = map.containsKey("receiveStatus") ? NumberUtils.toInt("" + map.get("receiveStatus"), 0) : 0;
			if (receiveStatus <= 0) {
				continue;
			}

			int dealerId = map.containsKey("dealerId") ? NumberUtils.toInt("" + map.get("dealerId"), 1) : 1;
			map.put("receiveStatusDesc", SmsApiStatus.getReceiveStatusDesc(dealerId, "" + map.get("receiveStatusCode")));
		}
	}


	/**
	 * 更新接收状态
	 * @param tableName  表名
	 * @param hwMsgId  华为返回的消息ID
	 * @param status  状态
	 * @return
	 */
	private int updateReceiveStatus(String tableName, String hwMsgId, Integer status, String statusCode) {
		String sql = "update " + tableName + " set receive_status = ?, receive_status_desc = ?, receive_time = now() where hw_msg_id = ?";
		return this.jdbcTemplate.update(sql, status, statusCode, hwMsgId);
	}

	/**
	 * 是否需要更新状态，WEB的全部更新， API失败的才需要更新
	 * @param smsReport  短信状态报告
	 * @return
	 */
	private boolean needUpdateReceiveStatus(SmsStatusReport smsReport) {
		// web发送的短信或者失败的都要更新
		return (   Constants.SENDER_TYPE_WEB.equals(smsReport.getSenderType())
				|| smsReport.getStatus().intValue() > 0
		);
	}

	/**
	 * 生成查询分表子查询
	 * @param userCode
	 * @param select
	 * @param where
	 * @param start
	 * @param end
	 * @return
	 */
	private String getSubSql(String userCode, String select, String where, LocalDateTime start, LocalDateTime end) {
		StringBuilder buffer = new StringBuilder(512);
		buffer.append("(");
		
		LocalDateTime now = start;
		if (end.getDayOfMonth() == 1) {
			end = end.minusDays(1);
		}
		
		while (true) {
			if (buffer.length() > 1) {
				buffer.append(" \n union all \n");
			}
			
			String table = TableUtil.getSendedRecordTable(userCode, now);
			buffer.append(" SELECT ")
			      .append(select)
			      .append(" FROM " )
			      .append(table)
			      .append(" WHERE ")
			      .append(where);
			
			now = now.plusMonths(1);
			if (this.needMoreTable(userCode, now, end)) {
				continue;
			} else {
				break;
			}
		}
		
		buffer.append(")");
		return buffer.toString();
	}

	/**
	 * 是否需要查询更多的表
	 * @param userCode
	 * @param start
	 * @param end
	 * @return
	 */
	private boolean needMoreTable(String userCode, LocalDateTime start, LocalDateTime end) {
		if (SmsUtil.isWebUser(userCode)) {
			// web用户的发送记录不分表
			return false;
		}
		
		if (start.getYear() < end.getYear()) {
			return true;
		}
		
		if (start.getMonthValue() > end.getMonthValue()) {
			return false;
		}
		
		return true;
	}

	/**
	 * 生成where过滤条件
	 * @param destNo
	 * @param content
	 * @param start
	 * @param end
	 * @param taskName
	 * @return
	 */
	private String getWhere(String userCode, String destNo, String content,
			LocalDateTime start, LocalDateTime end, String taskName) {
		String where = " \n user_code = " + userCode;
		
		if (start != null) {
			where += "  AND submit_time >= '" + LdtUtil.format(start) + "'";
		}
		
		if (end != null) {
			where += " AND submit_time < '" + LdtUtil.format(end) + "'";
		}
		
		if (!StringUtils.isEmpty(destNo)) {
			where += " AND dest_num like '" + destNo + "%'";
		}
		
		if (!StringUtils.isEmpty(content)) {
			where += " AND content like '%" + content + "%'";
		}
		
		if (!StringUtils.isEmpty(taskName)) {
			where += " AND task_name like '%" + taskName + "%' \n";
		}
		return where;
	}

}
